<?php
	include("initialize.php");
?>
<!--
This page handles the rendering of the class record either for
online editing or for printing.

Also includes a function to compute for the weighted average based
on the GradeGrid database.

@author Chad Estioco
@version Second Semester, AY 2010-2011
-->
<html>
<head>
	<style type="text/css">
		.schedule{
			border-style: solid;
			border-width: 1px;
			padding: 3px;
		}
		
		p, .pMirror{
			font-family: "Trebuchet MS", sans-serif;
			font-size: 13px;
			text-align: justify;
			color: #000000;
			margin-right: 8px;
		}
		
		input.noborder{
			border: none;
			border-color: #FFFFFF;
		}
		
		h1{
			font-family: Arial, Helvetica, sans-serif;
			font-size: 18px;
			color: #000000;
		}
	</style>
	
	<?php
		include ("write_functions.php");
		
		connect("localhost", "root", "", "gradegriddatabase");
		
		$classid = $_GET["classid"];
		
		//Query for the major requirements
		$getreqs_query = sprintf("SELECT name, major_id FROM major WHERE template_id IN
		                         (SELECT template_id FROM class WHERE class_id = '%s') ORDER BY major_id",
								 $classid);
		$grq = mysql_query($getreqs_query);
		$majors_limit = mysql_num_rows($grq);
		
		//Query for the students
		$findstudents_query = sprintf("SELECT last_name, first_name, account_id FROM account WHERE account_id IN
		                               (SELECT account_id FROM take WHERE class_id = '%s') ORDER BY last_name, first_name",
									   $classid);
		$fsq = mysql_query($findstudents_query);
		$student_limit = mysql_num_rows($fsq);
		
		/*
		We compute the weighted point average for each student. Note that we are discriminating
		between a grade that hasn't been input yet and a grade that is zero. We take the minor
		components of each major grade factor. The maxrawscores of each grade factor is kept
		in a running sum. We also store in a running sum the total points accumulated by a
		student. Afterwards, we divide this by the total maxrawscore. This quotient is then
		multiplied by the percentage weight of the major requirement associated with it.
		
		~The story of taking the weighted average. Bow.
		*/
		function computeGrade($classid){
			$student_index = 0;
			global $student_limit, $majors_limit, $fsq, $grq, $gmq;
			
			while($student_index < $student_limit){
				mysql_data_seek($fsq, $student_index);
				$students = mysql_fetch_array($fsq);
				$accountid = mysql_real_escape_string($students["account_id"]);
				$majors_index = 0;
				$running_grade = 0.0;
				
				while($majors_index < $majors_limit){
					mysql_data_seek($grq, $majors_index);
					$major = mysql_fetch_array($grq);
					$major_id = $major["major_id"];
					$getgrades_qs = sprintf("SELECT grade, maxrawscore, major.percentage FROM grade, minor, major WHERE account_id = '%s'
				                            AND minor.minor_id = grade.minor_id AND major.major_id = '%s' AND grade.class_id = '%s'",
				                            $accountid, $major_id,
							     			mysql_real_escape_string($classid));
					$grade_query = mysql_query($getgrades_qs);
					$grades_available = mysql_num_rows($grade_query);
					$grade_index = 0;
					$total_raw = 0;
					$major_sum = 0.00;
					$percentage = 0;
					
					while($grade_index < $grades_available){
						mysql_data_seek($grade_query, $grade_index);
						$grade_tuple = mysql_fetch_array($grade_query);
						$grade = (float) $grade_tuple["grade"];
						$maxrawscore = (float) $grade_tuple["maxrawscore"];
						
						$percentage = (float) $grade_tuple["percentage"];
						$major_sum += $grade;
						$total_raw += $maxrawscore;
						$grade_index++;
					}
					
					if($grades_available != 0){
						$major_sum /= $total_raw;
						$running_grade += $major_sum * $percentage;
					}
					
					$majors_index++;
				}
				
				$check_query = mysql_query("SELECT * FROM total WHERE account_id = '$accountid' AND class_id = '$classid'");
				$update_qs = sprintf("UPDATE total SET totalgrade = '%s' WHERE account_id = '%s' AND class_id = '%s'",
				                     mysql_real_escape_string($running_grade),
									 $accountid, $classid);
				$update_query = mysql_query($update_qs);
				if(mysql_num_rows($check_query) <= 0){
					$insert_qs = sprintf("INSERT INTO total(class_id, account_id, totalgrade) VALUES ('%s', '%s', %s)",
					                     $classid, $accountid, $running_grade);
					$insert_query = mysql_query($insert_qs);
				}
				
				$student_index++;
			}
		}
		
		//Handle if the page was generated (refreshed) by virtue of submitting
		//the form at the body.
		if(isset($_POST["gradegrid"])){
			/*
			In a loop, get all the minor requirements associated with each major requirement.
			Afterwards, with the student array, update the student accounts. While we're at it,
			update the total for each student.
			*/
			$student_index = 0;
			
			//Taking the students...
			while($student_index < $student_limit){
				mysql_data_seek($fsq, $student_index);
				$students = mysql_fetch_array($fsq);
				
				$majors_index = 0;
				$field_counter = 0;
				
				//Taking the major requirements associated with the student.
				while($majors_index < $majors_limit){
					mysql_data_seek($grq, $majors_index);
					$requirements = mysql_fetch_array($grq);
					$getminors_query = sprintf("SELECT * FROM minor WHERE major_id = '%s' ORDER BY name, minor_id",
												mysql_real_escape_string($requirements["major_id"]));
					$gmq = mysql_query($getminors_query);
					$minors_index = 0;
					$minors_limit = mysql_num_rows($gmq);
					
					//Taking the minor requirements associated with the major requirement...
					while($minors_index < $minors_limit){
						mysql_data_seek($gmq, $minors_index);
						$minors =  mysql_fetch_array($gmq);
						
						//I need the student's account_id, the major_id, the minor_id, ...
						$student_accountid = mysql_real_escape_string($students["account_id"]);
						$majorid = mysql_real_escape_string($requirements["major_id"]);
						$minorid = mysql_real_escape_string($minors["minor_id"]);
						//...and the class_id, which I already have from above.
						
						//Get the grade
						$majorname = str_replace(" ", "", $requirements["name"]);
						$grade = mysql_real_escape_string($_POST["$majorname$student_index$field_counter"]);
						
						$m = (int) $minors["maxrawscore"];
						$g = (int) $grade;
						
						if($m < $g){
							$minors_index++;
							$field_counter++;
							continue;
						}
						
						//Check if a tuple exists
						$tupleexists_qs = sprintf("SELECT class_id, major_id, minor_id, account_id FROM grade WHERE
						                           class_id = '%s' AND major_id = '%s' AND minor_id = '%s' AND account_id = '%s'",
												   $classid, $majorid, $minorid, $student_accountid);

						$tuplecount = mysql_num_rows(mysql_query($tupleexists_qs));
						$doesExist = (mysql_num_rows(mysql_query($tupleexists_qs)) > 0);
						
						//Assume first that this is a new grade and that a tuple for it does not exist yet.
						$insertgrade_qs = sprintf("INSERT INTO grade(class_id, major_id, minor_id, account_id, percentage, grade)
												   VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
												   $classid, $majorid, $minorid, $student_accountid, 'T', $grade);
						if($grade == ""){
						}else{
							if($doesExist){ //$insertgrade_query failed -- we have an existing record for it
								$updategrade_qs = sprintf("UPDATE grade SET grade = '%s' WHERE class_id = '%s' AND major_id = '%s' AND minor_id = '%s'
								                          AND account_id = '%s'",
												          $grade, $classid, $majorid, $minorid, $student_accountid);
								$updategrade = mysql_query($updategrade_qs);
								
								if(!$updategrade){
									throw new Exception("Failed updating the database.");
								}
							}else{
								$insertgrade_query = mysql_query($insertgrade_qs) or die(mysql_error() . $doesExist);
							}
						}
						
						$field_counter++;
						$minors_index++;
					}
					
					$majors_index++;
				}
				computeGrade($classid);
				$student_index++;
			}
		}
	?>
	
</head>
<body style="padding: 0px; margin: 0px;">
	<?php
		//In case this page was generated via an export command.
		$readonly = isset($_GET["printerfriendly"]);
		if($readonly){
			$account_query = sprintf("SELECT * FROM account WHERE username = '%s'",
		                             mysql_real_escape_string($_SESSION["username"]));
			$account = mysql_fetch_array(mysql_query($account_query));
		
			$class_query = sprintf("SELECT * FROM class WHERE class_id = '%s'",
		                           $classid);
			$class = mysql_fetch_array(mysql_query($class_query));
			$section = $class["class_name"];
			
			$course_query = sprintf("SELECT * FROM course WHERE course_id = '%s'",
		                            mysql_real_escape_string($class["course_id"]));
			$course = mysql_fetch_array(mysql_query($course_query));
			$classname = $course["course_name"];
			
			$school_query = sprintf("SELECT * FROM school WHERE school.account_id = '%s' and school.school_id = '%s'",
		                            mysql_real_escape_string($account["account_id"]),
								    mysql_real_escape_string($course["school_id"]));
			$school = mysql_fetch_array(mysql_query($school_query));
			
			if($school["school_name"] != null){
				echo "<h1 style='color: #B4B4B4'>" . $school['school_name'] . "<br />";
				echo $school['college_name'] . "<br />";
				echo $school['department_name'] . "</h1>";
			}
		    
			if($account["first_name"] != null && $account["last_name"] != null){
				echo "<p>Instructor: " . $account["first_name"] . " " . $account["last_name"] . "<br />";
				echo "Class: $classname - $section</p>";
			} else{
				echo "<p>Class: $classname - $section</p>";
			}
		}
	?>
	<?php
		echo "<form action='grades.php?classid=$classid' method='post' id='gradegrid'>\n";
		echo "<input type='textbox' name='gradegrid' style='display:none;' value='foo' />\n"; //Just a toggle field so we'll have means
		                                                                                      //to know if page was generated through form
																							  //submission.
		echo "<table class='schedule pMirror'>\n";
		$formnames = array();
		$minorids = array();
		$i = 0;
		$limit = mysql_num_rows($grq);
		$minlimit = 0;
		
		//Echo the student names in the table, alongside the minor requirements.
		//(See above.)
		echo "<tr>\n";
		echo "<td class='schedule'>Student Names</td>\n";
		while($i < $limit){
			mysql_data_seek($grq, $i);
			$requirements = mysql_fetch_array($grq);
			
			//...the minor requirements
			$getminors_query = sprintf("SELECT * FROM minor WHERE major_id = '%s' ORDER BY name, minor_id",
			                           mysql_real_escape_string($requirements["major_id"]));
			$gmq = mysql_query($getminors_query);
			$j = 0;
			$minors_limit = mysql_num_rows($gmq);
			$minlimit += $minors_limit;
			
			while($j < $minors_limit){
				mysql_data_seek($gmq, $j);
				$minors = mysql_fetch_array($gmq);
				echo "<td class='schedule'>" . $requirements["name"] . ": " . $minors["name"] . " (" . $minors["maxrawscore"] . ")" . "</td>\n";
				$fieldname = str_replace(" ", "", $requirements["name"]);
				array_push($formnames, $fieldname); //Take the names for the fields in the form (to be made unique
				                                    //by their matrix coordinates.)
				array_push($minorids, $minors["minor_id"]); //Take all the minorids associated with this record.
				$j++;
			}
			$i++;
		}
		echo "<td class='schedule'>Total</td>\n"; //And one last for the total
		echo "</tr>";
		
		$i = 0;
		$limit = mysql_num_rows($fsq);
		
		//Now output the fields for the grade
		while($i < $student_limit){
			mysql_data_seek($fsq, $i);
			$students = mysql_fetch_array($fsq);
			$accountid = mysql_real_escape_string($students["account_id"]);
			
			//Get the student grades
			$getgrades_query = sprintf("SELECT grade, major_id, minor_id, account_id FROM grade WHERE account_id='%s' and class_id IN
                                        (SELECT class_id FROM class, course WHERE class_id = '%s') ORDER BY major_id, minor_id",
			                           $accountid, $classid);
			
			$ggq = mysql_query($getgrades_query);
			$j = 0;
			$limit2 = mysql_num_rows($ggq);
			$namecounter = 0;
			$seek = true;
			
			echo "<tr>";
			echo "<td class='schedule'>". $students["last_name"] . ", " . $students["first_name"] . "</td>\n";
			
			/*
			Get all the available grades for the student. Since not all grades may be available
			for the moment, we still need to check if we are echo-ing under a column with an available
			grade. Note that this scheme relies heavily on the ordering of the queried tuples.
			*/
			while($j < $minlimit){
				if($j < $limit2 && $seek){
					mysql_data_seek($ggq, $j);
				}
				$grades = mysql_fetch_array($ggq);
				$name = $formnames[$namecounter] . $i . $j;
				
				if($grades["minor_id"] == $minorids[$namecounter]){ //If we are currently in the column for a recorded grade.
					if($readonly){
						echo "<td class='schedule'>" . $grades["grade"] . "</td>\n";
					}else{
						echo "<td class='schedule'><input type='text' class='noborder' value='" . $grades["grade"] . "' name='" . $name . "' /></td>\n";
					}
					$seek = true;
				} else{
					if($readonly){
						echo "<td class='schedule'></td>\n";
					}else{
						echo "<td class='schedule'><input type='text' class='noborder' value='' name='" . $name . "' /></td>\n";
					}
					$seek = false;
				}
				$j++;
				$namecounter++;
			}
			
			//And another one for the total.
			//Query the student's total if it exists.
			$studtotal_qs = sprintf("SELECT totalgrade FROM total WHERE class_id = '%s' AND account_id = '%s'",
			                        $classid, $accountid);
			$studtotal_select_query = mysql_query($studtotal_qs);
			
			if($studtotal_select_query){
				$studtotal_array = mysql_fetch_array($studtotal_select_query);
				$totalgrade = $studtotal_array["totalgrade"];
				echo "<td class='schedule'><input type='text' class='noborder' value='$totalgrade' name='total' readonly='readonly' /></td>\n";
			} else{
				echo "<td class='schedule'><input type='text' class='noborder' name='total' readonly='readonly' /></td>\n";
			}
			echo "</tr>";
			
			$i++;
		}
		echo "</table></form>";
	?>
	<?php
		//And now for some more printer-friendly effects.
		if($readonly){
			$deanset = $school["dean"] != null;
			$secretaryset = $school["college_secretary"] != null;
			$collegeset = $school["college_name"] != null;
			$deptchairset = $school["department_chair"] != null;
			$deptnameset = $school["department_name"] != null;
			
			if($deanset){
				echo "<p>" . $school["dean"] . "<br />";
				echo "Dean";
				
				if($collegeset){
					echo ", " . $school["college_name"];
				}
				
				echo "</p>";
			}
			
			if($secretaryset){
				echo "<p>" . $school["college_secretary"] . "<br />";
				echo "College Secretary";
				
				if($collegeset){
					echo ", " . $school["college_name"];
				}
				
				echo "</p>";
			}
			
			if($deptchairset){
				echo "<p>" . $school["department_chair"] . "<br />";
				echo "Department Chair";
				
				if($deptnameset){
					echo ", " . $school["department_name"] . "<br />";
				}
				
				echo "</p>";
			}
		}
	?>
</body>
</html>